﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using DHQGHN.DAL.Base;
using DHQGHN.DAL.Common;
using DHQGHN.DAL.Models.Admin;


namespace DHQGHN.DAL.DAO
{
    public class ExtractDAO : SqlDAO
    {
        public ExtractDAO(string connectionString) :
            base(connectionString) { }

        public static DataTable Extract(string connString, string cmdText)
        {
            DataTable dt = SqlHelper.GetDataTable(connString, cmdText);
            return dt;
        }

        public static DataTable ExtractTable(string SourceTable, string CONN_STRING_CONTROL, string CONN_STRING_SOURCE, string CONN_STRING_DESTINATION)
        {
            // DataTable dt = SqlHelper.GetDataTable(connString, cmdText);            
            SqlParameter[] spParameter = new SqlParameter[4];
            spParameter[0] = new SqlParameter("@TableName", SqlDbType.VarChar, 50);
            spParameter[0].Direction = ParameterDirection.Input;
            spParameter[0].Value = SourceTable;

            spParameter[1] = new SqlParameter("@TableName_out", SqlDbType.VarChar, 50);
            spParameter[1].Direction = ParameterDirection.Output;

            spParameter[2] = new SqlParameter("@CreateTableName_out", SqlDbType.VarChar, 1024);
            spParameter[2].Direction = ParameterDirection.Output;

            spParameter[3] = new SqlParameter("@SelectTableName_out", SqlDbType.VarChar, 1024);
            spParameter[3].Direction = ParameterDirection.Output;
            DataTable dt = SqlHelper.ExecuteTable(CONN_STRING_CONTROL, CommandType.StoredProcedure, "SDB_spGenarateTable", spParameter);

            return dt;
        }

        public bool ExtractData()
        {
            try
            {
                string clause = GetQuery();
                SqlCommand cmd = this.GetSqlQueryCommand(clause);

                int result = ExecuteNonQuery(cmd);

                return result > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        public ExtractReport GetReportDestination()
        {
            try
            {
                ExtractReport report = new ExtractReport();

                string areaQuery = "Select Count(*) From [TKTHDS_TDT_01].DM.AREA_DSKHHGD";
                string houseHoldQuery = "Select Count(*) From [TKTHDS_TDT_01].DIEUTRA.HOUSEHOLD_DSKHHGD ";
                string residentQuery = "Select Count(*) From [TKTHDS_TDT_01].DIEUTRA.RESIDENT_DSKHHGD";

                SqlCommand cmdArea = this.GetSqlQueryCommand(areaQuery);
                object resultArea = this.ExecuteScalar(cmdArea);

                SqlCommand cmdHouseHold = this.GetSqlQueryCommand(houseHoldQuery);
                object resultHouseHold = this.ExecuteScalar(cmdHouseHold);

                SqlCommand cmdResident = this.GetSqlQueryCommand(residentQuery);
                object resultResident = this.ExecuteScalar(cmdResident);

                if (resultArea != null)
                {
                    report.AreaRecordCount = Convert.ToInt32(resultArea);
                }

                if (resultHouseHold != null)
                {
                    report.HouseHoldRecordCount = Convert.ToInt32(resultHouseHold);
                }

                if (resultResident != null)
                {
                    report.ResidentRecodeCount = Convert.ToInt32(resultResident);
                }

                return report;
            }
            catch
            {
                throw;
            }
        }

        public ExtractReport GetReportSource()
        {
            try
            {
                ExtractReport report = new ExtractReport();

                string areaQuery = "Select Count(*) From [TKTHDS_CORE].DM.AREA_DSKHHGD";
                string houseHoldQuery = "Select Count(*) From [TKTHDS_CORE].CORE.HOUSEHOLD ";
                string residentQuery = "Select Count(*) From [TKTHDS_CORE].CORE.RESIDENT";

                SqlCommand cmdArea = this.GetSqlQueryCommand(areaQuery);
                object resultArea = this.ExecuteScalar(cmdArea);

                SqlCommand cmdHouseHold = this.GetSqlQueryCommand(houseHoldQuery);
                object resultHouseHold = this.ExecuteScalar(cmdHouseHold);

                SqlCommand cmdResident = this.GetSqlQueryCommand(residentQuery);
                object resultResident = this.ExecuteScalar(cmdResident);

                if (resultArea != null)
                {
                    report.AreaRecordCount = Convert.ToInt32(resultArea);
                }

                if (resultHouseHold != null)
                {
                    report.HouseHoldRecordCount = Convert.ToInt32(resultHouseHold);
                }

                if (resultResident != null)
                {
                    report.ResidentRecodeCount = Convert.ToInt32(resultResident);
                }

                return report;
            }
            catch
            {
                throw;
            }
        }

        private string GetQuery()
        {
            string clause = "DELETE FROM [TKTHDS_TDT_01].[DIEUTRA].[RESIDENT_DSKHHGD] " + Environment.NewLine +
                            "DELETE FROM [TKTHDS_TDT_01].[DIEUTRA].[HOUSEHOLD_DSKHHGD] " + Environment.NewLine +
                            "DELETE FROM [TKTHDS_TDT_01].[DM].[AREA_CONVERT] " + Environment.NewLine +
                            "DELETE FROM [TKTHDS_TDT_01].DM.AREA_DSKHHGD " + Environment.NewLine;

            string insertIntoArea = "insert into [TKTHDS_TDT_01].DM.AREA_DSKHHGD (AREA_DSKHHGD_ID, ADM_UNIT_ID,AREA_NAME, DESCRIPTIONS, DELETED, LAST_UPDATED) " + Environment.NewLine +
                                    "select AREA_DSKHHGD_ID, ADM_UNIT_ID,AREA_NAME, DESCRIPTIONS, DELETED, LAST_UPDATED " + Environment.NewLine +
                                    "FROM [TKTHDS_CORE].DM.AREA_DSKHHGD";

            string insertIntoHouseHold = "insert into [TKTHDS_TDT_01].DIEUTRA.HOUSEHOLD_DSKHHGD (HOUSEHOLD_ID, HOUSEHOLD_CODE, HOUSEHOLD_NAME, AREA_ID, DELETED )" + Environment.NewLine +
                                         "select HOUSEHOLD_ID, HOUSEHOLD_CODE, HOUSEHOLD_NAME, AREA_ID, DELETED  " + Environment.NewLine +
                                         "FROM [TKTHDS_CORE].CORE.HOUSEHOLD " + Environment.NewLine +
                                         "where AREA_ID in (select AREA_DSKHHGD_ID  from [TKTHDS_CORE].DM.AREA_DSKHHGD) ";

            string insertIntoResident = "insert into [TKTHDS_TDT_01].DIEUTRA.RESIDENT_DSKHHGD (RESIDENT_ID, HOUSEHOLD_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DATE_OF_BIRTH,  GENDER_ID, ETHNIC_ID, " + Environment.NewLine +
                            "DISABILITY_STATUS_ID, MARITAL_STATUS_ID, COUNTRY_ID, ADM_UNIT_ID, NP_ADM_UNIT_ID, " + Environment.NewLine +
                            "FATHER_LNAME, FATHER_MNAME, FATHER_FNAME,FATHER_DOB,FATHER_COUNTRY_ID,FATHER_RPOB_ADM_UNIT_ID,FATHER_ETHNIC_ID, " + Environment.NewLine +
                            "MOTHER_LNAME, MOTHER_MNAME, MOTHER_FNAME,MOTHER_DOB,MOTHER_COUNTRY_ID,MOTHER_RPOB_ADM_UNIT_ID,MOTHER_ETHNIC_ID, DELETED) " + Environment.NewLine +
                            "SELECT RESIDENT_ID, HOUSEHOLD_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, DATE_OF_BIRTH,  GENDER_ID, ETHNIC_ID, " + Environment.NewLine +
                            "DISABILITY_STATUS_ID, MARITAL_STATUS_ID, COUNTRY_ID, '13103' ADM_UNIT_ID, NP_ADM_UNIT_ID, " + Environment.NewLine +
                            "FATHER_LNAME, FATHER_MNAME, FATHER_FNAME,FATHER_DOB,FATHER_COUNTRY_ID,FATHER_RPOB_ADM_UNIT_ID,FATHER_ETHNIC_ID, " + Environment.NewLine +
                            "MOTHER_LNAME, MOTHER_MNAME, MOTHER_FNAME,MOTHER_DOB,MOTHER_COUNTRY_ID,MOTHER_RPOB_ADM_UNIT_ID,MOTHER_ETHNIC_ID, DELETED " + Environment.NewLine +
                            "FROM [TKTHDS_CORE].CORE.RESIDENT ";

            clause += insertIntoArea + Environment.NewLine;
            clause += insertIntoHouseHold + Environment.NewLine;
            clause += insertIntoResident;

            return clause;
        }

    }
}

